﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for DA_Contract
/// </summary>
public class DA_Contract
{
    AuditBillingEntities objABEnt;
    public DA_Contract()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public List<PLContract> DA_fnGetContractList(PLContract pObjContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from u in objABEnt.MstContracts
                          where (pObjContract.SearchIsActive == null || u.IsActive == pObjContract.SearchIsActive) &&
                                (pObjContract.ContractID == null || pObjContract.ContractID == u.MstContractID) &&
                                (string.IsNullOrEmpty(pObjContract.ContractName) || u.Name.ToLower().Contains(pObjContract.ContractName.ToLower()))
                          orderby u.Name
                          select new PLContract()
                          {
                              ContractID = u.MstContractID,
                              ContractName = u.Name,
                              Abbreviation = u.Abbreviation,
                              PropDurationMonths = u.PropDurationMonths,
                              ContractStartDate = u.ContractStartDate,
                              ContractStartEnd = u.ContractStartEnd,
                              IsActive = u.IsActive
                          });
            return varRes.ToList();
        }
    }

    public List<PLContract> DA_fnGetContractNameList(PLContract pObjContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from u in objABEnt.MstContracts
                          where (pObjContract.SearchIsActive == null ? true : (u.IsActive == pObjContract.SearchIsActive || u.MstContractID == pObjContract.AddContractID)) &&
                                (pObjContract.ContractID == null || pObjContract.ContractID == u.MstContractID)
                          orderby u.Name
                          select new PLContract()
                          {
                              ContractID = u.MstContractID,
                              ContractName = u.Name,
                              IsActive = u.IsActive
                          });
            return varRes.ToList();
        }
    }

    public int DA_InsertUpdateContract(PLContract pObjContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            MstContract objContract = new MstContract();
            if (pObjContract.ContractID.GetValueOrDefault() != 0)
            {
                objContract = (from u in objABEnt.MstContracts
                               where u.MstContractID == pObjContract.ContractID
                               select u).ToList().FirstOrDefault();
            }

            objContract.Name = pObjContract.ContractName;
            objContract.Abbreviation = pObjContract.Abbreviation;
            objContract.PropDurationMonths = pObjContract.PropDurationMonths;
            objContract.ContractStartDate = pObjContract.ContractStartDate;
            objContract.ContractStartEnd = pObjContract.ContractStartEnd;
            objContract.IsActive = pObjContract.IsActive.GetValueOrDefault();

            if (pObjContract.ContractID.GetValueOrDefault() != 0)
            {
                objContract.ModifiedBy = SessionInfo.prop_getObj.EmployeeID;
                objContract.ModifiedOn = DateTime.Now;
            }
            else
            {
                objContract.CreatedBy = SessionInfo.prop_getObj.EmployeeID;
                objContract.CreatedOn = DateTime.Now;
                objABEnt.MstContracts.Add(objContract);
            }
            objABEnt.SaveChanges();
            return objContract.MstContractID;
        }
    }

    public List<PLContract> DA_fnGetContractTransactions(PLContract pObjContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            List<int> lstArr;
            var varRes = (from cnt in objABEnt.TrnContracts
                          join c in objABEnt.MstContracts on cnt.MstContractID equals c.MstContractID
                          join com in objABEnt.MstCompanies on cnt.MstCompanyID equals com.MstCompanyID
                          join cli in objABEnt.MstClients on cnt.MstClientID equals cli.MstClientID
                          join pay in objABEnt.MstPaymentModes on cnt.MstPaymentModeID equals pay.MstPaymentModeID
                          where (cnt.FinancialYearID ?? 0) == 0
                          orderby cnt.ContractNo
                          let InvdAmt = ((objABEnt.TrnInvoices.Where(s => s.TrnContractID == cnt.TrnContractID).Sum(s => s.GrossInvoiceAmount)) ?? 0)
                          select new PLContract()
                          {
                              TrnContractID = cnt.TrnContractID,
                              ContractNo = cnt.ContractNo,
                              IsActive = cnt.IsActive,
                              IsCompleted = cnt.IsCompleted,
                              ContractStartDate = cnt.ContractStartDate,
                              ContractAmount = cnt.ContractAmount,

                              CompanyID = cnt.MstCompanyID,
                              ClientID = cnt.MstClientID,
                              ContractID = cnt.MstContractID,
                              PaymentModeID = cnt.MstPaymentModeID,

                              CompanyName = com.Name,
                              ContractName = c.Name,
                              ClientName = cli.ClientName,
                              PaymentModeName = pay.PaymentModeName,
                              InvoicedAmount = InvdAmt,
                              DueAmount = cnt.ContractAmount.Value - InvdAmt,
                              ContractFilePath = cnt.ContractFilePath

                          });

            if (!string.IsNullOrEmpty(pObjContract.SeachClientIDs))
            {
                lstArr = pObjContract.SeachClientIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.ClientID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjContract.SearchCompanyIDs))
            {
                lstArr = pObjContract.SearchCompanyIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.CompanyID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjContract.SearchContractIDs))
            {
                lstArr = pObjContract.SearchContractIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.ContractID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjContract.SearchPaymentModeIDs))
            {
                lstArr = pObjContract.SearchPaymentModeIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.PaymentModeID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjContract.TrnContractIDs))
            {
                lstArr = pObjContract.TrnContractIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.TrnContractID));
            }
            
            if (pObjContract.SearchIsActive != null)
                varRes = varRes.Where(s => s.IsActive == pObjContract.SearchIsActive);
            if (pObjContract.IsCompleted != null)
                varRes = varRes.Where(s => s.IsCompleted == pObjContract.IsCompleted);

            if (pObjContract.SearchContractStartDate != null)
                varRes = varRes.Where(s => s.ContractStartDate >= pObjContract.SearchContractStartDate);
            if (pObjContract.SearchContractEndDate != null)
                varRes = varRes.Where(s => s.ContractStartDate <= pObjContract.SearchContractEndDate);

            return varRes.ToList();
        }
    }

    public int DA_InsertUpdateContractTransactions(PLContract pObjContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            TrnContract objContract = new TrnContract();
            if (pObjContract.TrnContractID != 0)
            {
                objContract = (from u in objABEnt.TrnContracts
                               where u.TrnContractID == pObjContract.TrnContractID
                               select u).ToList().FirstOrDefault();
            }

            objContract.MstCompanyID = pObjContract.CompanyID.GetValueOrDefault();
            objContract.MstClientID = pObjContract.ClientID.GetValueOrDefault();
            objContract.MstContractID = pObjContract.ContractID.GetValueOrDefault();
            objContract.MstPaymentModeID = pObjContract.PaymentModeID.GetValueOrDefault();

            objContract.ContractStartDate = pObjContract.ContractStartDate.GetValueOrDefault();
            objContract.ContractAmount = pObjContract.ContractAmount.GetValueOrDefault();
            objContract.IsActive = pObjContract.IsActive.GetValueOrDefault();


            if (pObjContract.TrnContractID != 0)
            {
                objContract.ModifiedBy = SessionInfo.prop_getObj.EmployeeID;
                objContract.ModifiedOn = DateTime.Now;
            }
            else
            {
                string strCompanyName = objABEnt.MstCompanies.Where(s => s.MstCompanyID == objContract.MstCompanyID).FirstOrDefault().ShortName;
                strCompanyName = strCompanyName + DateTime.Now.ToString("yy");
                var varFirstRec = (from u in objABEnt.TrnContracts
                                   where u.ContractNo.Contains(strCompanyName)
                                   orderby u.TrnContractID descending
                                   select u.ContractNo).FirstOrDefault();
                if (varFirstRec == null)
                    objContract.ContractNo = strCompanyName + (1).ToString("00");
                else
                    objContract.ContractNo = (TrynParse.parseInt(varFirstRec.Replace(strCompanyName, "")) + 1).ToString("00");

                objContract.IsCompleted = false;
                objContract.CreatedBy = SessionInfo.prop_getObj.EmployeeID;
                objContract.CreatedOn = DateTime.Now;
                objABEnt.TrnContracts.Add(objContract);
            }
            objABEnt.SaveChanges();
            return objContract.TrnContractID;
        }
    }

    public void DA_SaveContractFilePath(PLContract pObjContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            if (pObjContract.TrnContractID != 0)
            {
                var objContract = (from u in objABEnt.TrnContracts
                                   where u.TrnContractID == pObjContract.TrnContractID
                                   select u).FirstOrDefault();
                objContract.ContractFilePath = pObjContract.ContractFilePath;
                objABEnt.SaveChanges();
            }
        }
    }
    public List<PLContract> DA_fnGetContractReport(PLContract pObjContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            List<int> lstArr;
            var varRes = (from cnt in objABEnt.TrnContracts
                          join c in objABEnt.MstContracts on cnt.MstContractID equals c.MstContractID
                          join com in objABEnt.MstCompanies on cnt.MstCompanyID equals com.MstCompanyID
                          join cli in objABEnt.MstClients on cnt.MstClientID equals cli.MstClientID
                          join pay in objABEnt.MstPaymentModes on cnt.MstPaymentModeID equals pay.MstPaymentModeID
                          join fin in objABEnt.FinancialYears on cnt.FinancialYearID equals fin.FinancialYearID into LO_fin
                          from fin in LO_fin.DefaultIfEmpty()
                          orderby cnt.ContractNo
                          select new PLContract()
                          {
                              TrnContractID = cnt.TrnContractID,
                              ContractNo = cnt.ContractNo,
                              IsActive = cnt.IsActive,
                              IsCompleted = cnt.IsCompleted,
                              ContractStartDate = cnt.ContractStartDate,
                              ContractAmount = cnt.ContractAmount,

                              CompanyID = cnt.MstCompanyID,
                              ClientID = cnt.MstClientID,
                              ContractID = cnt.MstContractID,
                              PaymentModeID = cnt.MstPaymentModeID,

                              CompanyName = com.Name,
                              ContractName = c.Name,
                              ClientName = cli.ClientName,
                              PaymentModeName = pay.PaymentModeName,
                              ContractFilePath = cnt.ContractFilePath,
                              FinancialYearID = cnt.FinancialYearID,
                              FinancialYearName = (fin == null ? "" : fin.FinYearName)
                          });

            if (!string.IsNullOrEmpty(pObjContract.SeachClientIDs))
            {
                lstArr = pObjContract.SeachClientIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.ClientID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjContract.SearchCompanyIDs))
            {
                lstArr = pObjContract.SearchCompanyIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.CompanyID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjContract.SearchContractIDs))
            {
                lstArr = pObjContract.SearchContractIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.ContractID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjContract.SearchPaymentModeIDs))
            {
                lstArr = pObjContract.SearchPaymentModeIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.PaymentModeID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjContract.FinancialYearIDs))
            {
                lstArr = pObjContract.FinancialYearIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.FinancialYearID ?? 0));
            }

            if (pObjContract.SearchIsActive != null)
                varRes = varRes.Where(s => s.IsActive == pObjContract.SearchIsActive);
            if (pObjContract.IsCompleted != null)
                varRes = varRes.Where(s => s.IsCompleted == pObjContract.IsCompleted);

            if (pObjContract.SearchContractStartDate != null)
                varRes = varRes.Where(s => s.ContractStartDate >= pObjContract.SearchContractStartDate);
            if (pObjContract.SearchContractEndDate != null)
                varRes = varRes.Where(s => s.ContractStartDate <= pObjContract.SearchContractEndDate);

            return varRes.ToList();
        }
    }
}